package com.jn.dagger.database;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.jn.dagger.entity.Student;

import java.util.ArrayList;
import java.util.List;

public class DBHelper extends SQLiteOpenHelper {

   private static final String TAG = "DBHelper";
   private static final String DATABASE_NAME = "db_student_manager";// 数据库名
   private static final int DATABASE_VERSION = 2;// 数据库版本号
   private static final String TABLE_NAME_STUDENT = "student"; //学生表
   private static final String TABLE_NAME_COURSE = "course"; //课程表

   public DBHelper(Context context) {
      super(context, DATABASE_NAME, null, DATABASE_VERSION);
   }

   @Override
   public void onCreate(SQLiteDatabase db) {
      initTable(db);
      initData(db);
      Log.d(TAG, "Database created and tables initialized.");
   }

   @Override
   public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
      if (oldVersion < 2) {
         db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_COURSE + " (" +
                 "course_id integer primary key autoincrement," +
                 "course_name text," +
                 "course_code text," +
                 "teacher_name text)");
         Log.d(TAG, "Course table created during upgrade.");
      }
      // 新版本后续可在此处进行其他更新
   }

   private void initTable(SQLiteDatabase db) {
      // 创建学生表
      db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_STUDENT + " (" +
              "_id integer primary key autoincrement," +
              "student_name text," +
              "student_code text," +
              "username text," +
              "password text," +
              "class_name text)");

      // 创建课程表
      db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_COURSE + " (" +
              "course_id integer primary key autoincrement," +
              "course_name text," +
              "course_code text," +
              "teacher_name text)");
   }

   private void initData(SQLiteDatabase db) {
      // 初始化学生数据
      db.execSQL("insert into " + TABLE_NAME_STUDENT +
              " (student_name , student_code, username, password, class_name) " +
              "values" +
              "('张老三', '202100000001', 'helloworld', '12345678', '计算机1班')");
      db.execSQL("insert into " + TABLE_NAME_STUDENT +
              " (student_name , student_code, username, password, class_name) " +
              "values" +
              "('李老四', '202100000002', 'helloworld', 'abcdefgh', '计算机2班')");
      db.execSQL("insert into " + TABLE_NAME_STUDENT +
              " (student_name , student_code, username, password, class_name) " +
              "values" +
              "('王老五', '202100000003', '你好和都很好的', 'aaaaaaaa', '计算机3班')");
      db.execSQL("insert into " + TABLE_NAME_STUDENT +
              " (student_name , student_code, username, password, class_name) " +
              "values" +
              "('刘老六', '202100000004', 'myname', '66665678', '计算机4班')");
   }

   public void insertStudent(Student student) {
      SQLiteDatabase db = getWritableDatabase();
      db.execSQL("insert into " + TABLE_NAME_STUDENT +
              " (student_name , student_code, username, password, class_name) " +
              "values" +
              "('" + student.getStudent_name() + "','" + student.getStudent_code() + "','" + student.getUsername() + "','" + student.getPassword() + "','" + student.getClass_name() + "')");
   }

   public void insertCourse(String courseName, String courseCode, String teacherName) {
      SQLiteDatabase db = getWritableDatabase();
      ContentValues values = new ContentValues();
      values.put("course_name", courseName);
      values.put("course_code", courseCode);
      values.put("teacher_name", teacherName);
      db.insert(TABLE_NAME_COURSE, null, values);
   }

   @SuppressLint("Range")
   public List<Student> selectAllStudent() {
      List<Student> list = new ArrayList<>();
      SQLiteDatabase db = getReadableDatabase();

      Cursor cursor = db.query(
              TABLE_NAME_STUDENT,     // 表名
              null,                   // 要查询的列数据
              null,                   // 条件语句
              null,                   // 条件语句的值
              null,                   // group by
              null,                   // having
              null                    // 排序语句
      );
      if (cursor != null && cursor.getCount() > 0) {
         while (cursor.moveToNext()) {
            Student student = new Student();
            student.setStudent_id(cursor.getInt(cursor.getColumnIndex("_id")));
            student.setStudent_name(cursor.getString(cursor.getColumnIndex("student_name")));
            student.setStudent_code(cursor.getString(cursor.getColumnIndex("student_code")));
            student.setUsername(cursor.getString(cursor.getColumnIndex("username")));
            student.setPassword(cursor.getString(cursor.getColumnIndex("password")));
            student.setClass_name(cursor.getString(cursor.getColumnIndex("class_name")));
            list.add(student);
         }
      }
      Log.d(TAG, "selectAllStudent = " + list.size());
      return list;
   }

   @SuppressLint("Range")
   public List<String> selectAllCourses() {
      List<String> list = new ArrayList<>();
      SQLiteDatabase db = getReadableDatabase();

      Cursor cursor = db.query(
              TABLE_NAME_COURSE,     // 表名
              null,                   // 要查询的列数据
              null,                   // 条件语句
              null,                   // 条件语句的值
              null,                   // group by
              null,                   // having
              null                    // 排序语句
      );
      if (cursor != null && cursor.getCount() > 0) {
         while (cursor.moveToNext()) {
            String courseName = cursor.getString(cursor.getColumnIndex("course_name"));
            String courseCode = cursor.getString(cursor.getColumnIndex("course_code"));
            String teacherName = cursor.getString(cursor.getColumnIndex("teacher_name"));
            list.add("课程名称: " + courseName + ", 课程代码: " + courseCode + ", 授课教师: " + teacherName);
         }
      }
      Log.d(TAG, "selectAllCourses = " + list.size());
      return list;
   }
}
